import ExcelJs from "exceljs"
import saveAs from "file-saver"
import moment from "moment"

export const wb = new ExcelJs.Workbook()
wb.creator = 'Cx';

export function saveAsExcel(name, sheet) {
    wb.xlsx.writeBuffer().then(buffer => {
        saveAs(new Blob([buffer], { type: "application/octet-stream"}), name || `excel导出.xlsx`)
        wb.removeWorksheet(sheet.id)
        window.$Common.closeFullLoading()
    })
}

export function saveAsExcelImage(name, sheet) {
    wb.xlsx.writeBuffer().then(buffer => {
        saveAs(new Blob([buffer], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}), name || `excel导出.xlsx`)
        wb.removeWorksheet(sheet.id)
        window.$Common.closeFullLoading()
    })
}

// 数据分析-数据报表导出
export function AnalysisReports(exportName, tableDatas) {
    let sheet = wb.addWorksheet("数据统计", { 
        properties: { defaultRowHeight: 20, defaultColWidth: 16 },
        views: [{ state: 'frozen', xSplit: 1, ySplit: 2 }]
    })
    let merges = ["A1:A2", "B1:F1", "G1:AH1", "AI1:AV1", "AW1:BF1", "BG1:BZ1", "CA1:CC1"]
    const tHeader2 = ["", "星期", "运营地区", "天气", "天气体验", "平均温度(℃)", 
                "总收入(元)", "钱包充值金额(元)", "骑行卡金额(元)", "会员卡金额(元)", "优惠卡金额(元)", "诚信金额(元)", "赠送金额(元)", "余额赠送(元)", "骑行卡赠送(元)", "会员卡赠送(元)", "优惠卡赠送(元)", "骑行费用(元)", "优惠金额(元)", "订单罚金(元)", "订单应收金额(元)", "订单实际金额(元)", "订单欠款金额(元)", "钱包结算金额(元)", "赠送余额结算(元)", "历史订单钱包支付(元)", "历史订单赠送余额支付(元)", "骑行卡优惠(元)", "折扣优惠(元)", "优惠卡优惠(元)", "其它优惠(元)", "钱包退款(元)", "充值退款(元)", "赠送退款(元)",
                "订单总数", "有效订单数", "有效订单率(%)", "实收订单数", "实收订单率(%)", "订单平均时长", "订单平均金额(元)", "骑行卡使用率(%)", "会员卡使用率(%)", "电单车低电丢单次数(次)", "电单车运维丢单次数(次)", "订单违停率(%)", "运营区内停车点外违停率(%)", "运营区外违停率(%)", 
                "新增注册数(人)", "未实名认证数(人)", "首骑用户数(人)", "有效骑行用户数(人)", "每日新增用户骑行转化率(%)", "总用户的骑行转化率(%)", "活跃用户(人)", "高活用户(人)", "新增复活用户数(人)", "新增流失用户数(人)", 
                "投放车周转率(%)", "可用车周转率(%)", "DAB周转率(%)", "总投放车(辆)", "可用车(辆)", "可用车占比(%)", "已报修(辆)", "已报修占比(%)", "低电量(辆)", "低电量占比(%)", "已下架(辆)", "其他状态车辆(辆)", "告警车辆(辆)", "WAB车辆数(辆)", "电单车DAB(辆)", "未骑行2天未处理(辆)", "7日未骑行车辆数(辆)", "未上报且2天未处理(辆)", "故障车2天未处理(辆)", "运营范围外2天未处理(辆)", 
                "换电量(次)", "挪车量(次)", "维修量(次)"]
    const notes = ["", "", "", "", "", "",
                "单日电车总收入（净入账=钱包充值-钱包退款+骑行卡收入+会员卡收入+优惠卡收入+诚信金收入）", "", "", "", "", "", "", "", "", "", "", "骑行费用=钱包结算金额+优惠金额+赠送余额结算", "优惠金额=骑行卡优惠+折扣优惠+优惠卡优惠+其它", "", "", "订单实际金额(骑行金额-优惠金额+罚金)", "截至到订单表格导入时，还未支付的金额", "", "", "历史订单中使用钱包支付的金额", "历史订单中使用赠送余额支付的金额", "", "", "", "其它优惠=优惠金额-骑行卡优惠-折扣优惠-优惠卡优惠", "钱包退款：从钱包退还给用户", "充值退款：退回余额", "赠送退款：退回到赠送余额", 
                "当天完成的所有订单", "骑行金额大于0的订单数", "有效订单占总订单的比例", "有实际收入的订单数", "已完成骑行并正常支付的订单比例（实收/有效）", "有效订单数量平均时间（排除超过1小时的长订单，计算所有订单的平均时间", "实收订单数量平均金额，实收金额/实收订单", "使用骑行卡结算/总有效订单", "使用会员结算/总有效订单", "扫码后显示低电数量", "扫码后显示运维中", "点位外还车订单/总订单", "点位外还车订单/（总订单-运营区外还车订单）", "运营区外还车订单/（总订单-运营区内点位外还车订单）", 
                "", "注册但是没有完成实名认证", "首骑，不限于是否新注册用户（活动中心固定活动，点击完成有效骑行活动的详情可筛选时间查看）", "单日活跃且产生有效订单的用户去重（通过导出当日订单列表，筛选重复数据后得出）", "当日新注册用户中完成有效骑行的比例（用当日新增用户和当日有效订单中的人名对比）", "所有已注册用户完成有效骑行的比例（所有注册用户对比有骑行订单的用户）", "7天内有1天骑行用户数", "7天内有4天骑行用户数", "大于30天无骑行用户复骑用户数", "新增的大于30天无订单用户用户数", 
                "有效订单/总投放车（剔除低于1分钟的订单后计算）", "有效订单/总可用车（总可用车辆=可使用车辆+骑行中车辆）", "有效订单/活跃可用车", "归属于该运营区的全部车辆", "可以骑行的车辆，总投放数-非可用车辆数", "可用车辆/总投放车", "", "已报修车辆/总投放车", "", "低电量车辆/总投放车", "", "调度中车辆数+换电中车辆数+拖回中车辆数", "", "七日内活跃车辆数", "单日活跃车辆数", "进入未骑行后（48小时未产单）2天没有进行调度等激活车辆的动作", "进入未骑行后（48小时未产单）7天没有进行调度等激活车辆的动作", "未上报（2天无位置上报）后2天无商家端操作", "", "骑出运营区域外后2天未处理车辆数", 
                "", "", ""]
    const commonAttrs = {
        border: { top: { style:'thin' }, left: { style:'thin' }, bottom: { style:'thin' }, right: { style:'thin' }},
        alignment: { vertical: 'middle', horizontal: 'center' },
        fill: { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFFFFF" } },
        font: { name: "微软雅黑" }
    }
    let columns = [
        { header: "日期", id: "date", width: 12 },

        { header: "综合信息", id: "week", width: 10 },
        { header: "", id: "area", width: 10 },
        { header: "", id: "weather", width: 10 },
        { header: "", id: "weatherExperience", width: 10 },
        { header: "", id: "rangeTemperature", width: 12 },

        { header: "营收数据", id: "revenue.totalRevenue", width: 12 },
        { header: "", id: "revenue.walletRechargeAmount", width: 16 },
        { header: "", id: "revenue.rideCardAmount", width: 14 },
        { header: "", id: "revenue.memberCardAmount", width: 14 },
        { header: "", id: "revenue.concessionCardAmount", width: 14 },
        { header: "", id: "revenue.honestyAmount", width: 12 },
        { header: "", id: "revenue.giftAmount", width: 12 },
        { header: "", id: "revenue.balanceGift", width: 12 },
        { header: "", id: "revenue.rideCardGift", width: 14 },
        { header: "", id: "revenue.memberCardGift", width: 14 },
        { header: "", id: "revenue.concessionCardGift", width: 14 },
        { header: "", id: "revenue.orderRideAmount", width: 12 },
        { header: "", id: "revenue.orderPreferentialAmount", width: 12 },
        { header: "", id: "revenue.orderPenaltyAmount", width: 12 },
        { header: "", id: "revenue.receivableAmount", width: 16 },
        { header: "", id: "revenue.orderAmount", width: 16 },
        { header: "", id: "revenue.orderArrears", width: 18 },
        { header: "", id: "revenue.orderWalletPayAmount", width: 16 },
        { header: "", id: "revenue.orderBalanceGiftPayAmount", width: 16 },
        { header: "", id: "revenue.historicalOrderWalletPayAmount", width: 20 },
        { header: "", id: "revenue.historicalOrderGiftPayAmount", width: 24 },
        { header: "", id: "revenue.rideCardPreferential", width: 14 },
        { header: "", id: "revenue.discountPreferential", width: 12 },
        { header: "", id: "revenue.concessionCardPreferential", width: 14 },
        { header: "", id: "revenue.otherPreferential", width: 12 },
        { header: "", id: "revenue.walletRefund", width: 12 },
        { header: "", id: "revenue.rechargeRefund", width: 12 },
        { header: "", id: "revenue.giftRefund", width: 12 },

        { header: "订单数据", id: "order.totalOrderCount", width: 10 },
        { header: "", id: "order.validOrderCount", width: 12 },
        { header: "", id: "order.validOrderRate", width: 14 },
        { header: "", id: "order.paidOrderCount", width: 12 },
        { header: "", id: "order.paidOrderRate", width: 14 },
        { header: "", id: "order.averageOrderDuration", width: 14 },
        { header: "", id: "order.averageOrderAmount", width: 16 },
        { header: "", id: "order.rideCardUsageRate", width: 16 },
        { header: "", id: "order.memberCardUsageRate", width: 16 },
        { header: "", id: "order.lowPowerLostOrderCount", width: 22 },
        { header: "", id: "order.maintenanceLostOrderCount", width: 22 },
        { header: "", id: "order.orderViolationRate", width: 13 },
        { header: "", id: "order.saOrderViolationRate", width: 24 },
        { header: "", id: "order.outOfSAOrderViolationRate", width: 16 },

        { header: "用户数据", id: "user.newRegistrationCount", width: 14 },
        { header: "", id: "user.unauthenticatedCount", width: 16 },
        { header: "", id: "user.firstRiderCount", width: 14 },
        { header: "", id: "user.validRiderCount", width: 18 },
        { header: "", id: "user.dailyNewRiderConversionRate", width: 24 },
        { header: "", id: "user.totalRiderConversionRate", width: 22 },
        { header: "", id: "user.activeUserCount", width: 12 },
        { header: "", id: "user.highActiveUserCount", width: 12 },
        { header: "", id: "user.newResurrectionCount", width: 18 },
        { header: "", id: "user.newLostCount", width: 18 },

        { header: "车辆数据", id: "vehicle.vehicleTurnoverRatio", width: 16 },
        { header: "", id: "vehicle.availableVehicleTurnoverRatio", width: 16 },
        { header: "", id: "vehicle.vehicleDabTurnoverRatio", width: 12 },
        { header: "", id: "vehicle.totalVehicleCount", width: 12 },
        { header: "", id: "vehicle.availableVehicleCount", width: 12 },
        { header: "", id: "vehicle.availableVehicleRatio", width: 14 },
        { header: "", id: "vehicle.reportedVehicleCount", width: 12 },
        { header: "", id: "vehicle.reportedVehicleRatio", width: 14 },
        { header: "", id: "vehicle.lowBatteryVehicleCount", width: 12 },
        { header: "", id: "vehicle.lowBatteryVehicleRatio", width: 14 },
        { header: "", id: "vehicle.offlineVehicleCount", width: 12 },
        { header: "", id: "vehicle.otherVehicleCount", width: 16 },
        { header: "", id: "vehicle.warningVehicleCount", width: 12 },
        { header: "", id: "vehicle.wabVehicleCount", width: 14 },
        { header: "", id: "vehicle.dabVehicleCount", width: 14 },
        { header: "", id: "vehicle.day2IUVehicleCount", width: 18 },
        { header: "", id: "vehicle.day7IUVehicleCount", width: 18 },
        { header: "", id: "vehicle.day2UUVehicleCount", width: 20 },
        { header: "", id: "vehicle.day2UFVehicleCount", width: 18 },
        { header: "", id: "vehicle.day2UOSAVehicleCount", width: 22 },

        { header: "运维数据", id: "operation.batterySwapCount", width: 12 },
        { header: "", id: "operation.moveCount", width: 12 },
        { header: "", id: "operation.maintenanceCount", width: 12 }
    ]
    // 设置列结构，第一行得标题
    let sheetColumns = []
    columns.forEach((x, i) => {
        let temp = {
            ...x,
            style: JSON.parse(JSON.stringify(commonAttrs))
        }
        if (i === 0) {
            temp.style.fill.fgColor = { argb: "FF8DB4E2" }
        }
        sheetColumns.push(temp)
    })
    sheet.columns = sheetColumns
    // 设置第二行标题
    sheet.getRow(2).values = tHeader2
    // 动态设置表格数据
    tableDatas.forEach((item, i) => {
        let tableRowData = columns.map(x => {
            return item[x.id]
        })
        sheet.getRow(i + 3).values = tableRowData
    })
    // 需要合并的操作
    merges.forEach(x => {
        sheet.mergeCells(x)
    })
    // 设置标题背景色
    let row1 = sheet.getRow(1)
    let row2 = sheet.getRow(2)
    columns.forEach((x, i) => {
        let rowIndex = i + 1
        if (i >= 1 && i <= 5) {
            row1.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FFFFC000" }}
            // row2.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FFFFC000" }}
        } else if (i >= 6 && i <= 33) {
            row1.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF92D050" }}
            // row2.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF92D050" }}
        } else if (i >= 34 && i <= 47) {
            row1.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF00B050" }}
            // row2.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF00B050" }}
        } else if (i >= 48 && i <= 57) {
            row1.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FFFFFF00" }}
            // row2.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FFFFFF00" }}
        } else if (i >= 58 && i <= 77) {
            row1.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF00B0F0" }}
            // row2.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF00B0F0" }}
        } else if (i >= 78 && i <= 81) {
            row1.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF0070C0" }}
            // row2.getCell(rowIndex).fill = { ...commonAttrs.fill, fgColor: { argb: "FF0070C0" }}
        }
        row1.getCell(rowIndex).font = { bold: true }
        if (notes[i]) {
            row2.getCell(rowIndex).note = notes[i]
        }
        // row2.getCell(rowIndex).font = { bold: true }
    })
    saveAsExcel(exportName || `每日数据统计(${moment().format("YYYY-MM-DD")}).xlsx`, sheet)
}

export function OrderBarCode(exportName, tableDatas) {
    let sheet = wb.addWorksheet("订单号条形码", { 
        properties: { defaultRowHeight: 50, defaultColWidth: 30 },
        views: [{ state: 'frozen', xSplit: 0, ySplit: 0 }]
    })
    const commonAttrs = {
        border: { top: { style:'thin' }, left: { style:'thin' }, bottom: { style:'thin' }, right: { style:'thin' }},
        alignment: { vertical: 'middle', horizontal: 'center', wrapText: 1 },
        fill: { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFFFFF" } },
        font: { name: "微软雅黑", size: 10 }
    }
    let columns = [
        { header: "订单号条形码", width: 30 }
    ]
    // 动态设置表格数据
    tableDatas.forEach((item, i) => {
        let tableRowData = columns.map((x, n) => {
            let imageId = wb.addImage({
                base64: item.barCodeItem.data,
                extension: "png"
            })
            sheet.addImage(imageId, {
                tl: { col: n, row: i },
                br: { col: (n + 1), row: (i + 1) }
            })
        })
    })
    
    // 设置列结构，第一行得标题
    let sheetColumns = []
    columns.forEach((x, i) => {
        commonAttrs.border = { top: null, left: null, bottom: null, right: null}
        let temp = {
            ...x,
            style: JSON.parse(JSON.stringify(commonAttrs))
        }
        sheetColumns.push(temp)
    })
    sheet.columns = sheetColumns
    saveAsExcel(exportName || `订单号条形码数据(${moment().format("YYYY-MM-DD")}).xlsx`, sheet)
}
